/*
 * Copyright (C) 2009 Jiannan Lu
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 2 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
 */

package com.restsql.db.sql;

import com.restsql.db.ColumnInfo;
import com.restsql.db.Database;
import com.restsql.util.collection.MultiHashValuedMap;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.List;
import java.util.Set;

/**
 * DerbyDesigner can be used for designing SQL commands for Derby databases.
 * @author jiann.lu@yahoo.com
 */
public class DerbyDesigner extends GenericSQLDesigner implements SQLDesigner {

    public DerbyDesigner(Database db) {
        super(db);
    }

    /**
     * Constructs a Select SQL command.
     * @return
     * @throws java.sql.SQLException
     * @throws InvalidInputDataException
     */
    @Override
    public String getSelectSQL() throws SQLException, InvalidInputDataException {
        final boolean qualified = tableCollection.size() > 1;//indicates whether column names should be qualified (i.e. table.column) or not (i.e. column)
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT ");//* FROM ( SELECT ROW_NUMBER() OVER() AS rownum, ");
        /* append columns */
        if (columnCollection.size() > 0) {
            for (int i = 0; i < columnCollection.size(); i++) {//iterate columns
                if (i > 0) {//a column already exists
                    sql.append(", ");//append comma
                }
                if (qualified) {//column names must be qualified
                    sql.append("" + columnCollection.get(i).getTable() + "." + columnCollection.get(i).getName());//append column
                } else {//column names can be unqualified
                    sql.append("" + columnCollection.get(i).getName() + "");//append column
                }
            }
        } else {
            for (int i = 0; i < tableCollection.size(); i++) {
                sql.append(tableCollection.get(i)+".*,");
            }
            sql.deleteCharAt(sql.lastIndexOf(","));
        }
        /* append FROM clause */
        sql.append(" FROM " + tableCollection.get(0) + "");//append the first table in source
        for (int i = 1; i < tableCollection.size(); i++) {//iterate tables starting from the second table in source
            final String table1 = tableCollection.get(i - 1);
            final String table2 = tableCollection.get(i);
            switch (joinCollection.get(i - 1)) {
                case INNER: {
                    sql.append(" INNER JOIN");
                    break;
                }
                case OUTER: {
                    sql.append(" OUTER JOIN");
                    break;
                }
                case LEFT: {
                    sql.append(" LEFT OUTER JOIN");
                    break;
                }
                case RIGHT: {
                    sql.append(" RIGHT OUTER JOIN");
                    break;
                }
            }
            sql.append(" " + table2 + "");
            sql.append(" ON");
            String[][] onColumns = this.db.getForeignKeyInfo().getColumns(table1, table2);
            for (int j = 0; j < onColumns.length; j++) {
                sql.append(" " + table1 + "." + onColumns[j][0] + " = " + table2 + "." + onColumns[j][1]);
                if (j < (onColumns.length - 1)) {
                    sql.append(" AND");
                }
            }
        }
        /* append WHERE clause */
        if (filters.size() != 0 || filterBoundaries.size() != 0) {//query contains filter or filterBoundaries
            sql.append(" WHERE ");
            if (filters.size() != 0 && filterBoundaries.size() > 1) {//query contains filter and more that one filterBoundaries
                sql.append("(");
            }
            for (int i = 0; i < filterBoundaries.size(); i++) {//iterate filter boundaries
                Filter f = filterBoundaries.get(i);
                if (qualified) {//column names must be qualified
                    sql.append("" + f.getTable() + "." + f.getColumn());
                } else {//column names can be unqualified
                    sql.append("" + f.getColumn() + "");
                }
                String tmp_value = f.getValue().matches("^[-+]?\\d*\\.?\\d*$") == true ? f.getValue() : "'" + f.getValue() + "'";// if datatype is charachtor then add  "'"
                sql.append( f.getOperator()+ tmp_value + "");
                if (i < (filterBoundaries.size() - 1)) {//more columns exist
                    sql.append(" AND ");//append AND to sql
                }
            }
            if (filters.size() != 0 && filterBoundaries.size() > 1) {//query contains filter and more that one filterBoundaries
                sql.append(")");
            }
            if (filters.size() != 0 && filterBoundaries.size() != 0) {//query contains filter and filterBoundaries
                sql.append(" AND ");//append AND to sql
            }
            if (filters.size() > 1 && filterBoundaries.size() != 0) {//query contains filterBoundaries and more than one filters
                sql.append("(");
            }
            for (int i = 0; i < filters.size(); i++) {//iterate filter
                Filter f = filters.get(i);
                if (qualified) {//column names must be qualified
                    sql.append("" + f.getTable() + "." + f.getColumn());
                } else {//column names can be unqualified
                    sql.append("" + f.getColumn() + "");
                }
                sql.append(" = " + f.getValue());
                if (i < (filters.size() - 1)) {//more columns exist
                    sql.append(" AND ");//append comma to sql
                }
            }
            if (filters.size() > 1 && filterBoundaries.size() != 0) {//query contains filterBoundaries and more than one filters
                sql.append(")");
            }
        }
        /* append ORDER BY clause */
        if (orderCollection.size() != 0) {//query contains order statements
            sql.append(" ORDER BY ");
            for (int i = 0; i < orderCollection.size(); i++) {//iterate columns
                if (qualified) {//column names must be qualified
                    sql.append("" + orderCollection.get(i).getTable() + "." + orderCollection.get(i).getColumn());//use table.column format to distinguish the columns
                } else {//column names can be unqualified
                    sql.append("" + orderCollection.get(i).getColumn() + "");
                }
                if (orderCollection.get(i).isAsc()) {
                    sql.append(" ASC");
                } else {
                    sql.append(" DESC");
                }
                if (i < (orderCollection.size() - 1)) {//more columns exist
                    sql.append(", ");//append comma to the sql
                }
            }
        }
        //sql.append(") ");//AS tmp WHERE rownum > "+(pageIndex>1?0:pageIndex-1)*pageSize+" AND rownum <= "+pageIndex*(pageSize)+"; ");
        /* append page limit */
        //sql.append(" LIMIT " + String.valueOf(pageIndex - 1) + "," + String.valueOf(pageSize) + ";");
        /* return sql command as a String */
        return sql.toString();
    }

    /**
     * Constructs a Insert SQL command.
     * @return
     * @throws java.sql.SQLException
     * @throws InvalidInputDataException
     */
    @Override
    public String getInsertSQL() throws SQLException, InvalidInputDataException {

        StringBuilder sql = new StringBuilder();
        if (tableCollection.size() == 1) {//iterate the tables of the query
            final String table = tableCollection.get(0);//get current table name
            StringBuilder cs = new StringBuilder();//String buffer for columns part
            StringBuilder vs = new StringBuilder();//String buffer for values part
            String key = "";
            String value = "";

            ColumnInfo columnInfo = db.getColumnInfo(table);
            Iterator it = values.entrySet().iterator();
            while (it.hasNext()) {
                java.util.Map.Entry entry = (java.util.Map.Entry) it.next();
                key = (String) entry.getKey();
                value = (String) ((List) entry.getValue()).get(0);//Assume get the first value
                if (columnInfo.exists(key)) {
                    if (cs.length() > 0) {
                        cs.append(", ");//append comma
                        vs.append(", ");//append comma
                    }
                    cs.append("" + key + "");//append column
                    vs.append(MySQLInputData.parse(db, table, key, value));//append value
                } else {
                    // Error there is no such column in specified table
                }               
            }
             sql.append("INSERT INTO " + table + " (");
                sql.append(cs);
                sql.append(") VALUES (");
                sql.append(vs);
                sql.append(") ");
        } else {
            //Error can not handle 2 or more than 2 tables insert
        }

        return sql.toString();
    }

       /**
     * Constructs a Delete SQL command.
     * @return
     * @throws java.sql.SQLException
     * @throws InvalidDataException
     */
    public String getDeleteSQL() throws SQLException, InvalidInputDataException {

        StringBuilder sql = new StringBuilder();
        if (tableCollection.size() == 1&&(filterBoundaries.size()>0)) {//iterate the tables of the query
                      final String table = tableCollection.get(0);//get current table name
            StringBuilder cs_vs = new StringBuilder();//String buffer for columns = values part
           
            StringBuilder filters_str = new StringBuilder();//String buffer for values part
            String key = "";
            String value = "";

            ColumnInfo columnInfo = db.getColumnInfo(table);
            for(Filter filer:filterBoundaries){
                filters_str.append(filer.getColumn()+" = "+DerbyInputData.parse(db, table, filer.getColumn(), filer.getValue()+" and "));
            }
             sql.append("DELETE FROM " + table + "");
                sql.append(" WHERE ");
                sql.append(filters_str.substring(0, filters_str.lastIndexOf("and")));

        } else {
            //Error can not handle 2 or more than 2 tables update,or resource is not a single instance
        }

        return sql.toString();
    }

    /**
     * Constructs an Update SQL command.
     * @return
     * @throws java.sql.SQLException
     * @throws InvalidDataException
     */
    public String getUpdateSQL() throws SQLException, InvalidInputDataException {

        StringBuilder sql = new StringBuilder();
        if ((tableCollection.size() == 1)&&(filterBoundaries.size()>0)) {//iterate the tables of the query
            final String table = tableCollection.get(0);//get current table name
            StringBuilder cs_vs = new StringBuilder();//String buffer for columns = values part
          
            StringBuilder filters_str = new StringBuilder();//String buffer for values part
            String key = "";
            String value = "";
            
            ColumnInfo columnInfo = db.getColumnInfo(table);
            for(Filter filer:filterBoundaries){
                filters_str.append(filer.getColumn()+" = "+DerbyInputData.parse(db, table, filer.getColumn(), filer.getValue()+" and "));
            }
            
            Iterator it = values.entrySet().iterator();
            while (it.hasNext()) {
                java.util.Map.Entry entry = (java.util.Map.Entry) it.next();
                key = (String) entry.getKey();
                value = (String) ((List) entry.getValue()).get(0);//Assume get the first value
                if (columnInfo.exists(key)) {
                    if (cs_vs.length() > 0) {
                        //cs.append(", ");//append comma
                        cs_vs.append(", ");//append comma
                    }
                    cs_vs.append("" + key + "= "+DerbyInputData.parse(db, table, key, value));//append column
                   
                } else {
                    // Error there is no such column in specified table
                }
            }
             sql.append("UPDATE " + table + " SET ");
                sql.append(cs_vs);
                sql.append(" WHERE ");
                sql.append(filters_str.substring(0, filters_str.lastIndexOf("and")));
 
        } else {
            //Error can not handle 2 or more than 2 tables update,or resource is not a single instance
        }

        return sql.toString();
    }
}
